CREATE PROCEDURE [dbo].[asi_FindOrderLine]
(
@orderNumber nvarchar(50),
@productKey uniqueidentifier,
@addressKey uniqueidentifier
)
AS
BEGIN
-- Find the order
declare @orderKey uniqueidentifier
declare @shipToKey uniqueidentifier

SELECT @orderKey = OrderKey from OrderMain
WHERE OrderMain.OrderNumber = @orderNumber

IF (@orderKey IS NULL)
BEGIN
RAISERROR ('Cannot find Order', 16, 1)
RETURN
END

SELECT     @shipToKey = OrderShipToKey
FROM         OrderShipTo
WHERE     (FullAddressKey = @addressKey) AND (OrderKey = @orderKey)

IF (@shipToKey IS NULL)
BEGIN
RAISERROR ('Cannot find ShipToKey', 16, 1)
RETURN
END

SELECT     OrderLine.*
FROM         OrderLine
WHERE     (OrderKey = @orderKey) AND (OrderShipToKey = @shipToKey) AND (ProductKey = @productKey)

END

GO